﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using Vaffanculo.Common.Constants;
using Vaffanculo.Migration.DataBase;
using Vaffanculo.Migration.SchemaMigration;
using ExcelLib = Microsoft.Office.Interop.Excel;

namespace Vaffanculo.Migration.ImplementsSchema.Excel
{
    public class ReadFileExcel
    {
        private readonly Hashtable _listConstanst;
        public List<ListDimentionOrFact> ListsDimentions { get; private set; }
        public List<ListDimentionOrFact> ListsFacts { get; private set; }
        public string PaternPath { get; set; }

        public IDataBaseTarget DataBaseTarget { private get; set; }

        public ReadFileExcel()
        {
            ListsDimentions = new List<ListDimentionOrFact>();
            ListsFacts = new List<ListDimentionOrFact>();
            _listConstanst = new Hashtable();

        }


        public void ReadDimentionOrFact(List<ISChemaDimention> schemaDimentions, List<ISchemaFact> schemaFacts, string  aPathFile,string aSheet)
        {
            ListsDimentions.Clear();
            ListsFacts.Clear();
            _listConstanst.Clear();
            InitMigration.Log.Info(String.Format("INICIANDO LA MIGRACION DEL ARCHIVO {0} A HORAS {1}",
                                                                   aPathFile,
                                                                   DateTime.Now));
            var application = new Application {Visible = false, ScreenUpdating = false, DisplayAlerts = false};


            Workbook workbook = application.Workbooks.Open(aPathFile, Missing.Value, Missing.Value, Missing.Value
                                                           , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                           , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                           , Missing.Value, Missing.Value, Missing.Value);

            Worksheet worksheet = workbook.Worksheets[1];
            if(aSheet != string.Empty)
            {
                foreach (Worksheet sheet in workbook.Worksheets)
                {
                    if (sheet.Name == aSheet)
                    {
                        worksheet = sheet;
                        break;
                    }
                }
            }
            
            
            
            foreach (var schemadimention in schemaDimentions)
            {
                if(!schemadimention.TypeDimention.Equals(MigrationConstants.TypeMigrationConstant))
                {
                    var listDimention = new ListDimentionOrFact
                                            {
                                                ListCellExcel = ReadDimention(worksheet, schemadimention),
                                                SearchName = schemadimention.SrchName,
                                                TableName = schemadimention.TableName,
                                                TypeMigration = schemadimention.TypeDimention,
                                                Replace = schemadimention.Replace,
                                                Name = schemadimention.Name,
                                                Format =  schemadimention.Format
                                            };
                    ListsDimentions.Add(listDimention);
                }
                else
                {
                    if(schemadimention.TableName!=null)
                    {
                        if(schemadimention.Source!=null)
                        {
                            switch(schemadimention.Source)
                            {
                                case "Source":
                                    string aux = schemadimention.TableName.Replace("dim", "id");
                                    string query = String.Format("SELECT {0} FROM {1} WHERE {2}='{3}'", aux, schemadimention.TableName,
                                                                            schemadimention.SrchName, PaternPath);
                                    DataSet dataSet = DataBaseTarget.ExecuteQuery(query);
                                     if (dataSet.Tables[0].Rows.Count < 1)
                                     {
                                        //todo: aqui debe hacerse la implementacion de dimensiones nuevas
                                        InitMigration.Log.Info(String.Format("No existe dimension {0} en la tabla {1}",
                                                                                PaternPath,
                                                                                schemadimention.TableName));
                                    }

                                     foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                                     {
                                         if(!_listConstanst.ContainsKey(aux))
                                             _listConstanst.Add(aux,dataRow[aux]);
                                     }    
                                    break;
                            }
                        }
                        

                    }
                    //todo: falta implementar cuando es una constante sin iteracion a la base de datos
                }

            }

            IList<ListDimentionOrFact> listDimentionsAux = new List<ListDimentionOrFact>();

            foreach (var schemaFact in schemaFacts)
            {
                if (!schemaFact.TypeFact.Equals(MigrationConstants.TypeMigrationIntersection))
                {

                    var listFact = new ListDimentionOrFact
                                       {
                                           ListCellExcel = ReadFact(worksheet, schemaFact),
                                           SearchName = schemaFact.FactName,
                                           TableName = schemaFact.TableName,
                                           TypeMigration = schemaFact.TypeFact,
                                       };
                    
                    ListsFacts.Add(listFact);
                }
                else
                {
                    var listFact = new ListDimentionOrFact
                    {
                        ListCellExcel = new List<CellExcel>(),
                        SearchName = schemaFact.FactName,
                        TableName = schemaFact.TableName,
                        TypeMigration = schemaFact.TypeFact
                    };

                    if (schemaFact.Composite != null)
                    {
                        listFact.Composite = new SChemaCompositeExcel
                        {
                            Composite = schemaFact.Composite.Composite,
                            Name = schemaFact.Composite.Name,
                            SrchName = schemaFact.Composite.SrchName
                        };
                    }
                    else
                        listFact.Composite = null;

                    var dimentioncontains = schemaFact.Crossings.Select(schemaCross => schemaCross.NameDimention).ToList();
                    IEnumerable<ListDimentionOrFact> dimentionColums = 
                        ListsDimentions.Where(dimention => dimentioncontains.Contains(dimention.Name)&&dimention.TypeMigration== MigrationConstants.TypeMigrationColumm);

                    IEnumerable<ListDimentionOrFact> dimentionRows = ListsDimentions.Where(dimention => dimentioncontains.Contains(dimention.Name) && dimention.TypeMigration == MigrationConstants.TypeMigrationRow);

                    
                    CreateQuery(dimentionRows, dimentionColums, listFact, worksheet.UsedRange);
                    //foreach (ListDimentionOrFact dimentionRow in dimentionsRows)

                    foreach (ListDimentionOrFact dimention in ListsDimentions)
                    {
                        if(dimentionRows.Contains(dimention)||dimentionColums.Contains(dimention))
                        {
                            if(!listDimentionsAux.Contains(dimention)) listDimentionsAux.Add(dimention);
                        }
                        
                    }

                }
            }

            Range range = worksheet.UsedRange;
            var values = (object[,])range.Value2;

            foreach (ListDimentionOrFact dimention in ListsDimentions)
            {
                if(listDimentionsAux.Contains(dimention))
                    continue;
                
                foreach (CellExcel  cellExcelDimention in dimention.ListCellExcel)
                {
                    var parameterQuery = new Hashtable();
                    string tablename = string.Empty;
                    string value;

                    foreach (ListDimentionOrFact fact in ListsFacts)
                    {
                        if(fact.TypeMigration.Equals(MigrationConstants.TypeMigrationIntersection))
                            continue;
                        tablename = fact.TableName;
                        foreach (CellExcel cellExcelFact in fact.ListCellExcel)
                        {
                            if(dimention.TypeMigration.Equals(MigrationConstants.TypeMigrationColumm) &&
                                fact.TypeMigration.Equals(MigrationConstants.TypeMigrationColumm))
                            {
                                value = values[cellExcelDimention.Row, cellExcelFact.Col] == null
                                            ? "NULL"
                                            : values[cellExcelDimention.Row, cellExcelFact.Col].ToString();
                                
                            }
                            else
                            {
                                if (dimention.TypeMigration.Equals(MigrationConstants.TypeMigrationColumm) &&
                                fact.TypeMigration.Equals(MigrationConstants.TypeMigrationRow))
                                {
                                    value = values[cellExcelFact.Row,cellExcelDimention.Col] == null
                                            ? "NULL"
                                            : values[cellExcelFact.Row, cellExcelDimention.Col].ToString();
                                }
                                else
                                {
                                    if (dimention.TypeMigration.Equals(MigrationConstants.TypeMigrationRow) &&
                                    fact.TypeMigration.Equals(MigrationConstants.TypeMigrationRow))
                                    {
                                        value = values[cellExcelFact.Row, cellExcelDimention.Col] == null
                                                ? "NULL"
                                                : values[cellExcelFact.Row, cellExcelDimention.Col].ToString();
                                    }
                                    else
                                    {
                                        //dimention.TypeMigration.Equals(MigrationConstants.TypeMigrationRow) &&fact.TypeMigration.Equals(MigrationConstants.TypeMigrationColumm)
                                        value = values[cellExcelFact.Row, cellExcelDimention.Col] == null
                                            ? "NULL"
                                            : values[cellExcelFact.Row, cellExcelDimention.Col].ToString();
                                    }
                                }

                            }


                            string aux = dimention.TableName.Replace("dim", "id");
                            string searchValue = cellExcelDimention.Value.ToString();
                            if (dimention.Replace.ContainsKey(searchValue))
                            {
                                //InitMigration.Log.Info(String.Format("Reemplazo de dimension {0} ; {1}",searchValue,dimention.Replace[searchValue].ToString()));
                                searchValue = dimention.Replace[searchValue].ToString();
                            }

                            if (searchValue.StartsWith("_"))
                                continue;

                            string query = String.Format("SELECT {0} FROM {1} WHERE {2}='{3}'", aux, dimention.TableName,
                                                            dimention.SearchName, searchValue);
                            DataSet dataSet = DataBaseTarget.ExecuteQuery(query);

                            if (dataSet.Tables[0].Rows.Count < 1)
                            {
                                //todo: aqui debe hacerse la implementacion de dimensiones nuevas
                                InitMigration.Log.Info(String.Format("No existe dimension {0} en la tabla {1}",
                                                                       cellExcelDimention.Value,
                                                                       dimention.TableName));
                            }

                            foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                            {
                                if (!parameterQuery.ContainsKey(fact.SearchName))
                                    parameterQuery.Add(fact.SearchName, value);
                                if (!parameterQuery.ContainsKey(aux))
                                    parameterQuery.Add(aux, dataRow[aux]);

                            }
                            
                        }
                    }
                    if (parameterQuery.Count > 0)
                    {
                        foreach (string key in _listConstanst.Keys)
                        {
                            if(!parameterQuery.ContainsKey(key))
                                parameterQuery.Add(key,_listConstanst[key]);
                        }
                        InitMigration.Log.Info(DataBaseTarget.CreateInsertQuery(parameterQuery, tablename));
                    }
                }
               

            }

            application.Quit();
            ReleaseObject(worksheet);
            ReleaseObject(workbook);
            ReleaseObject(application);

            InitMigration.Log.Info(String.Format("FIN DE LA MIGRACION DEL ARCHIVO {0} A HORAS {1}",
                                                                   aPathFile,
                                                                   DateTime.Now));
        }


        private void CreateQuery(IEnumerable dimentionsRows, IEnumerable dimentionsColumns, ListDimentionOrFact fact, Range range)
        {
            var values = (object[,])range.Value2;
            foreach (ListDimentionOrFact dimentionRow in dimentionsRows)
            {
                foreach (CellExcel cellExcelRow in dimentionRow.ListCellExcel)
                {
                    foreach (ListDimentionOrFact dimentionColumn in dimentionsColumns)
                    {
                        foreach (CellExcel cellExcelColumn in dimentionColumn.ListCellExcel)
                        {
                            bool composite = false;
                            var parameterQuery = new Hashtable();
                            string tablename = fact.TableName;
                            string value;

                            value = values[cellExcelColumn.Row, cellExcelRow.Col] == null
                                           ? "NULL"
                                           : values[cellExcelColumn.Row, cellExcelRow.Col].ToString();

                            //InitMigration.Log.Info(String.Format("columna {0}; fila {1} ; valor {2}",cellExcelColumn.Row,cellExcelRow.Col,value));

                            if (fact.Composite != null)
                            {
                                string auxDim = fact.Composite.Name.Replace("dim", "id");
                                Hashtable hashtableDimentionAux = new Hashtable();

                                string searchValueRow = cellExcelRow.Value.ToString();
                                foreach (string key in dimentionRow.Replace.Keys)
                                {
                                    if (searchValueRow.Trim().StartsWith(key))
                                    {
                                        searchValueRow = dimentionRow.Replace[key].ToString();
                                        break;
                                    }
                                }
                                if (searchValueRow.StartsWith("_"))
                                    continue;

                                hashtableDimentionAux.Add(dimentionRow.Name,searchValueRow);

                                string searchValueCol = cellExcelColumn.Value.ToString();
                                foreach (string key in dimentionRow.Replace.Keys)
                                {
                                    if (searchValueCol.Trim().StartsWith(key))
                                    {
                                        searchValueCol = dimentionColumn.Replace[key].ToString();
                                        break;
                                    }
                                }
                                if (searchValueCol.StartsWith("_"))
                                    continue;
                                hashtableDimentionAux.Add(dimentionColumn.Name, searchValueCol);

                                string auxsearchValue = CompositeDimention(fact.Composite.Composite,
                                                                           hashtableDimentionAux);
                                
                                string query = String.Format("SELECT {0} FROM {1} WHERE {2}='{3}'", auxDim,
                                                             fact.Composite.Name,
                                                             fact.Composite.SrchName, auxsearchValue);
                                DataSet dataSet = DataBaseTarget.ExecuteQuery(query);
                                if(dataSet==null) continue;

                                if (dataSet.Tables[0].Rows.Count < 1)
                                {
                                    //todo: aqui debe hacerse la implementacion de dimensiones nuevas
                                    InitMigration.Log.Info(String.Format("No existe dimension {0} en la tabla {1}",
                                                                         cellExcelRow.Value,
                                                                         dimentionRow.TableName));
                                }

                                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                                {
                                    if (!parameterQuery.ContainsKey(fact.SearchName))
                                        parameterQuery.Add(fact.SearchName, value);
                                    if (!parameterQuery.ContainsKey(auxDim))
                                        parameterQuery.Add(auxDim, dataRow[auxDim]);
                                }
                                composite = true;
                            }
                            else
                            {
                                #region dimention
                                string auxRow = dimentionRow.TableName.Replace("dim", "id");
                                string searchValueRow = cellExcelRow.Value.ToString();


                                foreach (string key in dimentionRow.Replace.Keys)
                                {
                                    if (searchValueRow.Trim().StartsWith(key))
                                    {
                                        searchValueRow = dimentionRow.Replace[key].ToString();
                                        break;
                                    }
                                }
                                if (searchValueRow.StartsWith("_"))
                                    continue;

                                searchValueRow = FormatDimention(searchValueRow, dimentionRow.Format);

                                string query = String.Format("SELECT {0} FROM {1} WHERE {2}='{3}'", auxRow,
                                                             dimentionRow.TableName,
                                                             dimentionRow.SearchName, searchValueRow);

                                //InitMigration.Log.Info(String.Format("consulta 1 {0}",query));

                                DataSet dataSet = DataBaseTarget.ExecuteQuery(query);

                                if (dataSet.Tables[0].Rows.Count < 1)
                                {
                                    //todo: aqui debe hacerse la implementacion de dimensiones nuevas
                                    InitMigration.Log.Info(String.Format("No existe dimension {0} en la tabla {1}",
                                                                         cellExcelRow.Value,
                                                                         dimentionRow.TableName));
                                }

                                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                                {
                                    if (!parameterQuery.ContainsKey(auxRow))
                                        parameterQuery.Add(auxRow, dataRow[auxRow]);

                                }

                                string auxColumn = dimentionColumn.TableName.Replace("dim", "id");
                                string searchValueColumn = cellExcelColumn.Value.ToString();


                                foreach (string key in dimentionColumn.Replace.Keys)
                                {
                                    if (searchValueColumn.Trim().StartsWith(key))
                                    {
                                        searchValueColumn = dimentionColumn.Replace[key].ToString();
                                        break;
                                    }
                                }
                                if (searchValueColumn.StartsWith("_"))
                                    continue;

                                searchValueColumn = FormatDimention(searchValueColumn, dimentionColumn.Format);

                                query = String.Format("SELECT {0} FROM {1} WHERE {2}='{3}'", auxColumn,
                                                      dimentionColumn.TableName,
                                                      dimentionColumn.SearchName, searchValueColumn);

                                //InitMigration.Log.Info(String.Format("consulta 2 {0}", query));
                                dataSet = DataBaseTarget.ExecuteQuery(query);

                                if (dataSet.Tables[0].Rows.Count < 1)
                                {
                                    //todo: aqui debe hacerse la implementacion de dimensiones nuevas
                                    InitMigration.Log.Info(String.Format("No existe dimension {0} en la tabla {1}",
                                                                         cellExcelColumn.Value,
                                                                         dimentionColumn.TableName));
                                }

                                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                                {
                                    if (!parameterQuery.ContainsKey(fact.SearchName))
                                        parameterQuery.Add(fact.SearchName, value);
                                    if (!parameterQuery.ContainsKey(auxColumn))
                                        parameterQuery.Add(auxColumn, dataRow[auxColumn]);

                                }

                                #endregion 
                                
                            }
                            if (parameterQuery.Count > 2||composite)
                            {
                                foreach (string key in _listConstanst.Keys)
                                {
                                    if (!parameterQuery.ContainsKey(key))
                                        parameterQuery.Add(key, _listConstanst[key]);
                                }
                                InitMigration.Log.Info(DataBaseTarget.CreateInsertQuery(parameterQuery, tablename));
                            }
                        }
                        
                    }
                    
                }
            }
        }

       

        private static void ReleaseObject(object obj)
        {
            try
            {
                Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception )
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }

        private static List<CellExcel> ReadFact(Worksheet worksheet, ISchemaFact schemaFact)
        {
            var result = new List<CellExcel>();
            Range range = worksheet.UsedRange;
            var values = (object[,])range.Value2;

            bool terminate = false;

            for (int i = 1; i < values.GetLength(0); i++)
            {
                if (terminate)
                    break;
                for (int j = 1; j < values.GetLength(1); j++)
                {
                    if (values[i, j] != null)
                    {
                        string aux = values[i, j].ToString();
                        if (aux.ToLower().StartsWith(schemaFact.StartWith.ToLower()))
                        {
                            var cellExcel = new CellExcel { Col = j, Row = i, Value = aux.ToLower().Trim() };
                            result.Add(cellExcel);
                            terminate = true;
                            break;
                        }
                    }
                }
            }
            return result;
        }

        private static List<CellExcel> ReadDimention(Worksheet worksheet, ISChemaDimention schemaDimention)
        {
            
            var result = new List<CellExcel>();

            Range range = worksheet.UsedRange;
            var values = (object[,])range.Value2;

            int initRow = 0;//row
            int initColumn = 0;//colum
            bool terminate = false;
            for (int i = 1; i < values.GetLength(0); i++)
            {
                if(terminate)
                    break;
                for (int j = 1; j < values.GetLength(1); j++)
                {
                    if (values[i, j] != null)
                    {
                        string aux = values[i, j].ToString();
                        if (aux.ToLower().StartsWith(schemaDimention.StartWith.ToLower()))
                        {
                            initRow = i;
                            initColumn = j;
                            terminate = true;
                            break;
                        }
                    }
                }
            }

            if (initColumn == 0 && schemaDimention.TypeDimention.Equals(MigrationConstants.TypeMigrationRow))
                return result;
            if (initRow == 0 && schemaDimention.TypeDimention.Equals(MigrationConstants.TypeMigrationColumm))
                return result;

            if(schemaDimention.TypeDimention.Equals(MigrationConstants.TypeMigrationRow))
            {
                for (int i = initColumn; i < values.GetLength(1); i++)
                {
                    if (schemaDimention.SearchDeep!=null)
                        if(i>int.Parse(schemaDimention.SearchDeep)) break;

                    if (values[initRow, i] != null)
                    {
                        string aux = values[initRow, i].ToString();
                        if (aux.Trim() != "")
                        {
                            var cellExcel = new CellExcel { Col = i, Row = initRow, Value = aux.ToLower().Trim() };
                            result.Add(cellExcel);
                        }
                    }
                }
            }
            else
            {
                for (int j = initRow; j < values.GetLength(0); j++)
                {
                    if (schemaDimention.SearchDeep != null)
                        if (j > int.Parse(schemaDimention.SearchDeep)) break;

                    if (values[j, initColumn] != null)
                    {
                        string aux = values[j, initColumn].ToString();
                        if (aux.Trim() != "")
                        {
                            var cellExcel = new CellExcel { Col = initColumn, Row = j, Value = aux.ToLower().Trim() };
                            result.Add(cellExcel);
                        }
                    }
                }
                
            }

            return result;
        }


        private string FormatDimention(string valueCell, string dimentionFormat)
        {
            if (valueCell == "NULL") return valueCell;

            string result;
            switch (dimentionFormat)
            {
                case MigrationConstants.Time:
                     DateTime aux = new DateTime(1900, 1, 1);
                    int diaaux = int.Parse(valueCell);
                    aux = aux.AddDays(diaaux-2);
                    result = aux.ToString("yyyy-MM-dd");
                    break;
                default:
                    result = valueCell;
                    break;
            }
            return result;
        }
        

        private string CompositeDimention(string formCoposite, Hashtable dimentions)
        {
            string result = formCoposite;
            foreach (string key in dimentions.Keys)
            {
                result = result.Replace("dim(" + key + ")", dimentions[key].ToString());
            }
            return result;
        }
    }
}
